package helper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSON;

/**
 * 数据库字段
 *
 */
public class FieldUtils {
	
	
	public static String turnType(String type) {
		if (type.toLowerCase().startsWith("int")
				|| type.toLowerCase().startsWith("tinyint")
				|| type.toLowerCase().startsWith("integer")
				|| type.toLowerCase().startsWith("smallint")
				|| type.toLowerCase().startsWith("mediumint")
				|| type.toLowerCase().startsWith("bigint")
				|| type.toLowerCase().startsWith("number"))
			return "int";
		if (type.toLowerCase().startsWith("double")
				|| type.toLowerCase().startsWith("float")
				|| type.toLowerCase().startsWith("decimal")
				|| type.toLowerCase().startsWith("numeric"))
			return "double";
		if (type.toLowerCase().startsWith("char")
				|| type.toLowerCase().startsWith("varchar")
				|| type.toLowerCase().startsWith("longvarchar"))
			return "String";
		if (type.toLowerCase().startsWith("time")
				|| type.toLowerCase().startsWith("date")
				|| type.toLowerCase().startsWith("datetime")
				|| type.toLowerCase().startsWith("timestamp"))
			return "java.util.Date";
		if (type.toLowerCase().startsWith("real"))
			return "float";
		if (type.equals("java.lang.String"))
			return "String";
		return type;
	}
	
	public static void main(String[] args) {
		System.out.println( JSON.toJSONString(getTableFieldMap("student","com.mysql.jdbc.Driver","jdbc:mysql://127.0.0.1:3306/frame_work","root","root")));
		System.out.println( JSON.toJSONString(getTableFieldMap("TS_MENU_INFO","oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@202.101.116.85:1510:XDWDB","ct_ecp","ct_ecp")));
	    System.out.println(turnType("String"));
 }
	
    /**
     * 获得数据表字段
     * @return
     */
	public static Map<String, String>[] getTableFieldMap(String table,
			String driverClassName, String url, String username, String password) {
		Map<String, String>[] fieldMap = new HashMap[2];
		fieldMap[0]=new HashMap<String,String>();
		fieldMap[1]=new HashMap<String,String>();
		if (driverClassName == null || url == null || username == null
				|| password == null)
			return fieldMap;

		Statement st = null;
		Connection c = null;
		try {
			Class.forName(driverClassName);
			c = DriverManager.getConnection(url, username, password);
			st = c.createStatement();
			String sql = "";
			if("oracle.jdbc.driver.OracleDriver".equals(driverClassName)){
				sql = "select T1.COLUMN_NAME,t1.DATA_TYPE,t2.COMMENTS from user_tab_columns t1, user_col_comments t2 "+
				" where T1.table_name =upper('"+table+"') AND t1.TABLE_NAME  =t2.TABLE_NAME AND t1.COLUMN_NAME =t2.COLUMN_NAME";
				System.out.println(sql);
				ResultSet rs = st.executeQuery(sql);
				while(rs.next()){
					String name = rs.getString("COLUMN_NAME").toLowerCase();
					String type = rs.getString("DATA_TYPE");
					String comment = rs.getString("COMMENTS");
					// System.out.println("-------name="+name+".-------,type="+type);
					type = turnType(type);
					fieldMap[0].put(name, type);
					fieldMap[1].put(name, comment);
				}
			}
			if("com.mysql.jdbc.Driver".equals(driverClassName)){
				sql = "SHOW FULL COLUMNS FROM " + table;
				ResultSet rs = st.executeQuery(sql);
				while(rs.next()){
					String name = rs.getString("Field").toLowerCase();
					String type = rs.getString("Type");
					String comment = rs.getString("Comment");
					// System.out.println("-------name="+name+".-------,type="+type);
					type = turnType(type);
					fieldMap[0].put(name, type);
					fieldMap[1].put(name, comment);
				}
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return fieldMap;
	}

	public static List<String> getTableFieldList(String table,
			String driverClassName, String url, String username, String password) {
		List<String> fieldList = new ArrayList<String>();
		if (driverClassName == null || url == null || username == null
				|| password == null)
			return fieldList;

		Statement st = null;
		Connection c = null;
		try {
			Class.forName(driverClassName);
			c = DriverManager.getConnection(url, username, password);
			st = c.createStatement();
			ResultSet rs = st.executeQuery("desc " + table);
			while (rs.next()) {
				String name = rs.getString(1);
				fieldList.add(name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return fieldList;
	}
}
